package wwj.dao;

import wwj.bean.CommodityBean;
import wwj.bean.HomeData;
import wwj.utils.SqlUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;

public class CommodityDao {

    /**
     * 查询主页搜索信息
     */
    public static HomeData selectBySearch(HomeData homeData) {
        System.out.println("执行搜索方法");
        // 获取Connection连接
        Connection connection = SqlUtil.getConnection();
        // 系统错误
        if (connection == null) {
            homeData.setTotalElement(-1);  // 如果系统异常设置全部数据-1
            return homeData;
        }
        ArrayList<CommodityBean> list = new ArrayList<CommodityBean>();  // 结构集装入集合
        homeData.setKeyWord("%" + homeData.getKeyWord() + "%");
        String sql = "select * from (select ROW_NUMBER() over(order by id asc)as line,* from commodity where info like ?)commodity ";
        String sqlPage = sql + "where line>? and line<=?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sqlPage);
            preparedStatement.setString(1, homeData.getKeyWord());
            System.out.println("当前line" + (homeData.getCurPage() - 1) * homeData.getNumPerPage() + "       " + (homeData.getCurPage()) * homeData.getNumPerPage());
            preparedStatement.setInt(2, (homeData.getCurPage() - 1) * homeData.getNumPerPage());
            preparedStatement.setInt(3, (homeData.getCurPage()) * homeData.getNumPerPage());
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String info = resultSet.getString("info");
                String imgPath = resultSet.getString("imgPath");
                int sort = resultSet.getInt("sort");
                BigDecimal price = resultSet.getBigDecimal("price");
                int storage = resultSet.getInt("storage");
                CommodityBean commodityBean = new CommodityBean(id, info, imgPath, sort, price, storage);
                list.add(commodityBean);
            }

            for (CommodityBean commodityBean : list) {
                System.out.println("查询结果集：" + commodityBean);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            if (list.size() != 0) {
                homeData.setCommodity(list);
                preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                preparedStatement.setString(1, homeData.getKeyWord());
                ResultSet rsTotal = preparedStatement.executeQuery();
                rsTotal.last(); //移到最后一条数据
                homeData.setTotalElement(rsTotal.getRow());
                System.out.println("总记录数为：" + rsTotal.getRow());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String replace = homeData.getKeyWord().replace("%", "");  // 返回给前端需要去除百分号
        homeData.setKeyWord(replace);

        return homeData;
    }


    /**
     * 查询homeData数据
     */
    public static void selectHomeDateBySort(HomeData homeData) {
        Connection connection = SqlUtil.getConnection();
        if (connection == null) {
            return;
        }
        /*
         * 分类和不分类的SQL语句
         */
        String totalSql = null;
        if (homeData.getSort() == 0) {
            totalSql = "select * from (select ROW_NUMBER() over(order by id asc)as line,* from commodity)commodity";
        } else {
            totalSql = "select * from (select ROW_NUMBER() over(order by id asc)as line,* from commodity where sort = ?)commodity";
        }
        String pageSql = totalSql + " where line>? and line<=?";

        PreparedStatement ps = null;
        /*
         * 连接分页SQL语句之后，对where中的第几条到第几条做计算，注入SQL
         */
        try {
            ps = connection.prepareStatement(pageSql);
            if (homeData.getSort() == 0) {
                ps.setInt(1, (homeData.getCurPage() - 1) * homeData.getNumPerPage());
                ps.setInt(2, (homeData.getCurPage()) * homeData.getNumPerPage());
            } else {
                ps.setInt(1, homeData.getSort());
                ps.setInt(2, (homeData.getCurPage() - 1) * homeData.getNumPerPage());
                ps.setInt(3, (homeData.getCurPage()) * homeData.getNumPerPage());
            }
            /*
             * 执行SQL，封装结果集到实体类
             */
            ResultSet rs = ps.executeQuery();
            ArrayList<CommodityBean> list = new ArrayList<CommodityBean>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String info = rs.getString("info");
                String imgPath = rs.getString("imgPath");
                int sort = rs.getInt("sort");
                BigDecimal price = rs.getBigDecimal("price");
                int storage = rs.getInt("storage");
                CommodityBean commodityBean = new CommodityBean(id, info, imgPath, sort, price, storage);
                list.add(commodityBean);
            }
            /*
             * 根据判断有分类和误分类选择不同的SQL语句，将最后一条数据的行号取出得出总记录数
             */
            if (list.size() != 0) {
                homeData.setCommodity(list);
                ps = connection.prepareStatement(totalSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                if (homeData.getSort() != 0) {
                    ps.setInt(1, homeData.getSort());
                }
                ResultSet rsTotal = ps.executeQuery();
                rsTotal.last(); //移到最后一条数据
                homeData.setTotalElement(rsTotal.getRow());
                System.out.println(homeData);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static CommodityBean getCommodityById(int id) {
        CommodityBean commodityBean = null;
        Connection connection = SqlUtil.getConnection();
        String sql = "select * from commodity where id =?";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int cid = rs.getInt("id");
                String info = rs.getString("info");
                String imgPath = rs.getString("imgPath");
                int sort = rs.getInt("sort");
                BigDecimal price = rs.getBigDecimal("price");
                int storage = rs.getInt("storage");
                commodityBean = new CommodityBean(cid, info, imgPath, sort, price, storage);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return commodityBean;
    }

}
